package com.kuhh.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.kuhh.dao.IPageBeanDao;
import com.kuhh.pojo.*;
import com.kuhh.utils.JdbcUtils;

public class PageBeanDaoImpl implements IPageBeanDao{
	
	private Connection con;
	private PreparedStatement pre;
	private ResultSet resultSet;

	@Override
	public List<Grade> findGradeByPage(int currPage, int pageSize) {
		List<Grade> list = new ArrayList<>();
		try {
			con = JdbcUtils.getConnection();
			String sql = "select * from tb_grade order by create_time desc limit ?,?";
			pre = con.prepareStatement(sql);
			pre.setInt(1, (currPage-1)*pageSize);
			pre.setInt(2, pageSize);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				Grade grade = new Grade();
				grade.setGradeNo(resultSet.getString(1));
				grade.setGradeName(resultSet.getString(2));
				grade.setTeacherNo(resultSet.getString(3));
				grade.setCreateTime(resultSet.getDate(4));
				grade.setUpdateTime(resultSet.getDate(5));
				list.add(grade);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return list;
	}


	@Override
	public List<College> findCollegeByPage(int currPage, int pageSize) {
		List<College> list = new ArrayList<College>();
		try {
			con = JdbcUtils.getConnection();
			String sql = "select * from tb_college order by create_time desc limit ?,?";
			pre = con.prepareStatement(sql);
			pre.setInt(1, (currPage-1)*pageSize);
			pre.setInt(2, pageSize);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				College college = new College();
				college.setCollegeNo(resultSet.getString(1));
				college.setCollegeName(resultSet.getString(2));
				college.setCreateTime(resultSet.getDate(3));
				college.setUpdateTime(resultSet.getDate(4));
				list.add(college);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return list;
	}

	@Override
	public List<Profession> findProfessionByPage(int currPage, int pageSize) {
		List<Profession> list = new ArrayList<>();
		try {
			con = JdbcUtils.getConnection();
			String sql = "select * from tb_profession order by create_time desc limit ?,?";
			pre = con.prepareStatement(sql);
			pre.setInt(1, (currPage-1)*pageSize);
			pre.setInt(2, pageSize);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				Profession profession = new Profession();
				profession.setProfessionNo(resultSet.getString(1));
				profession.setProfessionName(resultSet.getString(2));
				profession.setCollegeNo(resultSet.getString(3));
				profession.setCreateTime(resultSet.getTime(4));
				profession.setUpdateTime(resultSet.getTime(5));
				list.add(profession);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return list;
	}

	@Override
	public List<ProfessionClass> findProfessionClassByPage(int currPage, int pageSize) {
		List<ProfessionClass> list = new ArrayList<ProfessionClass>();
		try {
			con = JdbcUtils.getConnection();
			String sql = "select * from tb_profession_class order by create_time desc limit ?,?";
			pre = con.prepareStatement(sql);
			pre.setInt(1, (currPage-1)*pageSize);
			pre.setInt(2, pageSize);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				ProfessionClass professionClass = new ProfessionClass();
				professionClass.setClassNo(resultSet.getString(1));
				professionClass.setClassName(resultSet.getString(2));
				professionClass.setProfessionNo(resultSet.getString(3));
				professionClass.setTeacherNo(resultSet.getString(4));
				professionClass.setCreateTime(resultSet.getTime(5));
				professionClass.setUpdateTime(resultSet.getTime(6));
				list.add(professionClass);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return list;
	}

	@Override
	public List<ProfessionCourse> findProfessionCourseByPage(int currPage, int pageSize) {
		List<ProfessionCourse> list = new ArrayList<ProfessionCourse>();
		try {
			con = JdbcUtils.getConnection();
			String sql = "select * from tb_profession_course order by create_time desc limit ?,?";
			pre = con.prepareStatement(sql);
			pre.setInt(1, (currPage-1)*pageSize);
			pre.setInt(2, pageSize);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				ProfessionCourse professionCourse = new ProfessionCourse();
				professionCourse.setCourseNo(resultSet.getString(1));
				professionCourse.setCourseName(resultSet.getString(2));
				professionCourse.setCourseType(resultSet.getString(3));
				professionCourse.setCourseCredit(resultSet.getString(4));
				professionCourse.setCourseNum(resultSet.getInt(5));
				professionCourse.setGradeNo(resultSet.getString(6));
				professionCourse.setProfessionNo(resultSet.getString(7));
				professionCourse.setTeacherNo(resultSet.getString(8));
				professionCourse.setCreateTime(resultSet.getTime(9));
				professionCourse.setUpdateTime(resultSet.getTime(10));
				list.add(professionCourse);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return list;
	}

	@Override
	public List<ElectiveStudent> findElectiveStudentByPage(int currPage, int pageSize) {
		List<ElectiveStudent> list = new ArrayList<ElectiveStudent>();
		try {
			con = JdbcUtils.getConnection();
			String sql = "select id,profession_no,course_no,student_no,create_time,update_time from tb_elective_student order by create_time desc limit ?,?";
			pre = con.prepareStatement(sql);
			pre.setInt(1, (currPage-1)*pageSize);
			pre.setInt(2, pageSize);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				ElectiveStudent electiveStudent = new ElectiveStudent();
				electiveStudent.setId(resultSet.getInt(1));
				electiveStudent.setProfessionNo(resultSet.getString(2));
				electiveStudent.setCourseNo(resultSet.getString(3));
				electiveStudent.setStudentNo(resultSet.getString(4));
				electiveStudent.setCreateTime(resultSet.getTime(5));
				electiveStudent.setUpdateTime(resultSet.getTime(6));
				list.add(electiveStudent);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return list;
	}

	@Override
	public List<Scores> findScoresByPage(int currPage, int pageSize) {
		List<Scores> list = new ArrayList<>();
		try {
			con = JdbcUtils.getConnection();
			String sql = "select id,course_no,student_no,score,grade_point,create_time,update_time from tb_scores order by create_time desc limit ?,?";
			pre = con.prepareStatement(sql);
			pre.setInt(1, (currPage-1)*pageSize);
			pre.setInt(2, pageSize);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				Scores scores = new Scores();
				scores.setId(resultSet.getInt(1));
				scores.setCourseNo(resultSet.getString(2));
				scores.setStudentNo(resultSet.getString(3));
				scores.setScore(resultSet.getFloat(4));
				scores.setGradePoint(resultSet.getInt(5));
				scores.setCreateTime(resultSet.getTime(6));
				scores.setUpdateTime(resultSet.getTime(7));
				list.add(scores);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return list;
	}
	@Override
	public List<Students> findStudentsByPage(int currPage, int pageSize) {
		List<Students> list = new ArrayList<>();
		try {
			con = JdbcUtils.getConnection();
			String sql = "select * from tb_students order by create_time desc limit ?,?";
			pre = con.prepareStatement(sql);
			pre.setInt(1, (currPage - 1) * pageSize);
			pre.setInt(2, pageSize);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				Students student = new Students();
				student.setStudentNo(resultSet.getString(1));
				student.setStudentName(resultSet.getString(2));
				student.setGradeNo(resultSet.getString(3));
				student.setCollegeNo(resultSet.getString(4));
				student.setProfessionNo(resultSet.getString(5));
				student.setClassNo(resultSet.getString(6));
				student.setDescription(resultSet.getString(7));
				student.setIdCard(resultSet.getString(8));
				student.setAge(resultSet.getInt(9));
				student.setGender(resultSet.getByte(10));
				student.setYear(resultSet.getString(11));
				student.setCreateTime(resultSet.getDate(12));
				student.setUpdateTime(resultSet.getDate(13));
				list.add(student);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return list;
	}

	@Override
	public List<Teachers> findTeachersByPage(int currPage, int pageSize) {
		List<Teachers> list = new ArrayList<>();
		try {
			con = JdbcUtils.getConnection();
			String sql = "select * from tb_teachers order by create_time desc limit ?,?";
			pre = con.prepareStatement(sql);
			pre.setInt(1, (currPage - 1) * pageSize);
			pre.setInt(2, pageSize);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				Teachers teacher = new Teachers();
				teacher.setTeacherNo(resultSet.getString(1));
				teacher.setTeacherName(resultSet.getString(2));
				teacher.setGender(resultSet.getByte(3));
				teacher.setCreateTime(resultSet.getDate(4));
				teacher.setUpdateTime(resultSet.getDate(5));
				list.add(teacher);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return list;
	}

	@Override
	public List<User> findUserByPage(int currPage, int pageSize) {
		List<User> list = new ArrayList<>();
		try {
			con = JdbcUtils.getConnection();
			String sql = "select * from tb_user where username != 'admin' order by create_time desc limit ?,?";
			pre = con.prepareStatement(sql);
			pre.setInt(1, (currPage - 1) * pageSize);
			pre.setInt(2, pageSize);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				Integer userId = resultSet.getInt("user_id");
				Byte userType = resultSet.getByte("user_type");
				Byte state = resultSet.getByte("state");
				Date createTime = resultSet.getDate("create_time");
				Date updateTime = resultSet.getDate("update_time");
				String password = resultSet.getString("password");
				String psd = resultSet.getString("psd");
				String username = resultSet.getString("username");
				String displayName = resultSet.getString("display_name");
				User user = new User();
				user.setUserId(userId);
				user.setUserType(userType);
				user.setPassword(password);
				user.setPsd(psd);
				user.setUsername(username);
				user.setState(state);
				user.setCreateTime(createTime);
				user.setUpdateTime(updateTime);
				user.setDisplayName(displayName);
				list.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return list;
	}

	@Override
	public int getGradeCount() {
		try {
			con = JdbcUtils.getConnection();
			String sql = "select count(*) from tb_grade";
			pre = con.prepareStatement(sql);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				int num = resultSet.getInt(1);
				return num;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return 0;
	}

	@Override
	public int getCollegeCount() {
		try {
			con = JdbcUtils.getConnection();
			String sql = "select count(*) from tb_college";
			pre = con.prepareStatement(sql);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				int num = resultSet.getInt(1);
				return num;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return 0;
	}

	@Override
	public int getProfessionCount() {
		try {
			con = JdbcUtils.getConnection();
			String sql = "select count(*) from tb_profession";
			pre = con.prepareStatement(sql);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				int num = resultSet.getInt(1);
				return num;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return 0;
	}

	@Override
	public int getProfessionClassCount() {
		try {
			con = JdbcUtils.getConnection();
			String sql = "select count(*) from tb_profession_class";
			pre = con.prepareStatement(sql);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				int num = resultSet.getInt(1);
				return num;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return 0;
	}

	@Override
	public int getProfessionCourseCount() {
		try {
			con = JdbcUtils.getConnection();
			String sql = "select count(*) from tb_profession_course";
			pre = con.prepareStatement(sql);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				int num = resultSet.getInt(1);
				return num;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return 0;
	}

	@Override
	public int getElectiveStudentCount() {
		try {
			con = JdbcUtils.getConnection();
			String sql = "select count(*) from tb_elective_student";
			pre = con.prepareStatement(sql);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				int num = resultSet.getInt(1);
				return num;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return 0;
	}

	@Override
	public int getScoresCount() {
		try {
			con = JdbcUtils.getConnection();
			String sql = "select count(*) from tb_scores";
			pre = con.prepareStatement(sql);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				int num = resultSet.getInt(1);
				return num;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return 0;
	}

	@Override
	public int getStudentsCount() {
		try {
			con = JdbcUtils.getConnection();
			String sql = "select count(*) from tb_students";
			pre = con.prepareStatement(sql);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				int num = resultSet.getInt(1);
				return num;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return 0;
	}

	@Override
	public int getTeachersCount() {
		try {
			con = JdbcUtils.getConnection();
			String sql = "select count(*) from tb_teachers";
			pre = con.prepareStatement(sql);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				int num = resultSet.getInt(1);
				return num;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return 0;
	}

	@Override
	public int getUserCount() {
		try {
			con = JdbcUtils.getConnection();
			String sql = "select count(*) from tb_user";
			pre = con.prepareStatement(sql);
			resultSet = pre.executeQuery();
			while(resultSet.next()) {
				int num = resultSet.getInt(1);
				return num;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtils.close(con, pre, resultSet);
		}
		return 0;
	}


}
